package com.vksyu.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.primefaces.model.UploadedFile;

import com.vksyu.model.CommonData;
import com.vksyu.model.CommonResults;

public class FileUtil {

	private final static String[] UNIVERSYTY_ROW_HEADERS = { "ID", "Назва",
			"Індекс Якості Науково-педагогічного персоналу",
			"Індекс якості навчання", "Індекс міжнародного визнання", "ВКСЮ" };
	private final static String[] JORNALS_ROW_HEADERS = { "ID", "Назва",
			"Якість видання", "Міжнародні індексації", "Зміст статей", "ВКСЮ" };
	private final static String[] RESULTS_ROW_HEADERS = { "ID", "Назва",
			"Якість персоналу", "Якість навчання", "Міжнародне визнання",
			"Якість видання", "Міжнародні індексації", "Зміст статей", "ВКСЮ" };

	public static boolean isFileEXEL(UploadedFile file) {
		String[] fileNameParts = file.getFileName().split("\\.");
		if (fileNameParts.length < 2)
			return false;

		String extension = file.getFileName().split("\\.")[1];
		if (extension.equals("xlsx") || extension.equals("xls"))
			return true;

		return false;
	}

	public static boolean isFileXLSX(UploadedFile file) {
		if (file.getFileName().split("\\.")[1].equals("xlsx"))
			return true;

		return false;
	}

	public static Sheet createXLSSheet(UploadedFile file, int sheetNumber) {
		HSSFWorkbook wb;
		Sheet sheet = null;
		try {
			wb = new HSSFWorkbook(file.getInputstream());
			sheet = wb.getSheetAt(sheetNumber);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return sheet;
	}

	public static Sheet createXLSXSheet(UploadedFile file, int sheetNumber) {
		XSSFWorkbook wb;
		Sheet sheet = null;
		try {
			wb = new XSSFWorkbook(file.getInputstream());
			sheet = wb.getSheetAt(sheetNumber);
		} catch (IOException e) {
			e.printStackTrace();
		}
		return sheet;
	}

	public static List<CommonData> readExelSheet(Sheet sheet) {
		List<CommonData> commonData = new ArrayList<CommonData>();
		try {

			Row row;

			int rows = sheet.getPhysicalNumberOfRows();

			for (int r = 1; r < rows; r++) {
				row = sheet.getRow(r);

				if (row != null) {
					commonData.add(new CommonData((int) row.getCell(0)
							.getNumericCellValue(), row.getCell(1)
							.getStringCellValue(), row.getCell(2)
							.getNumericCellValue(), row.getCell(3)
							.getNumericCellValue(), row.getCell(4)
							.getNumericCellValue()));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return commonData;
	}

	private static void writeExelSheetInputData(Sheet sheet,
			List<CommonData> data, String[] headers) {
		for (int r = 0; r < 1; r++) {
			Row row = sheet.createRow(r);
			for (int i = 0; i < headers.length - 1; i++) {
				row.createCell(i).setCellValue(headers[i]);
				;
			}
		}

		for (int r = 0; r < data.size(); r++) {
			Row row = sheet.createRow(r + 1);
			row.createCell(0).setCellValue(data.get(r).getId());
			row.createCell(1).setCellValue(data.get(r).getName());
			row.createCell(2).setCellValue(data.get(r).getParam1());
			row.createCell(3).setCellValue(data.get(r).getParam2());
			row.createCell(4).setCellValue(data.get(r).getParam3());
		}
	}

	private static void writeExelSheetOutputData(Sheet sheet,
			List<CommonData> data, String[] headers) {
		for (int r = 0; r < 1; r++) {
			Row row = sheet.createRow(r);
			for (int i = 0; i < headers.length; i++) {
				row.createCell(i).setCellValue(headers[i]);
				;
			}
		}

		for (int r = 0; r < data.size(); r++) {
			Row row = sheet.createRow(r + 1);
			row.createCell(0).setCellValue(data.get(r).getId());
			row.createCell(1).setCellValue(data.get(r).getName());
			row.createCell(2).setCellValue(data.get(r).getParam1());
			row.createCell(3).setCellValue(data.get(r).getParam2());
			row.createCell(4).setCellValue(data.get(r).getParam3());
			row.createCell(5).setCellValue(data.get(r).getVksyu());
		}
	}

	private static void writeExelSheetResultsData(Sheet sheet,
			List<CommonResults> results, String[] headers) {
		for (int r = 0; r < 1; r++) {
			Row row = sheet.createRow(r);
			for (int i = 0; i < headers.length; i++) {
				row.createCell(i).setCellValue(headers[i]);
				;
			}
		}

		for (int r = 0; r < results.size(); r++) {
			Row row = sheet.createRow(r + 1);
			row.createCell(0).setCellValue(results.get(r).getId());
			row.createCell(1).setCellValue(results.get(r).getName());
			row.createCell(2).setCellValue(results.get(r).getParam1());
			row.createCell(3).setCellValue(results.get(r).getParam2());
			row.createCell(4).setCellValue(results.get(r).getParam3());
			row.createCell(5).setCellValue(results.get(r).getParam4());
			row.createCell(6).setCellValue(results.get(r).getParam5());
			row.createCell(7).setCellValue(results.get(r).getParam6());
			row.createCell(8).setCellValue(results.get(r).getVksyu());
		}
	}

	public static FileInputStream getWorkbookInputStream(
			List<CommonData> universityInput, List<CommonData> jornalsInput,
			List<CommonData> universityResults,
			List<CommonData> jornalsResults, List<CommonResults> results)
			throws FileNotFoundException {

		Workbook wb = new HSSFWorkbook();
		Sheet sheet1 = wb.createSheet("UniversityInput");
		writeExelSheetInputData(sheet1, universityInput, UNIVERSYTY_ROW_HEADERS);

		Sheet sheet2 = wb.createSheet("JornalsInput");
		writeExelSheetInputData(sheet2, jornalsInput, JORNALS_ROW_HEADERS);

		Sheet sheet3 = wb.createSheet("UniversityResults");
		writeExelSheetOutputData(sheet3, universityResults,
				UNIVERSYTY_ROW_HEADERS);

		Sheet sheet4 = wb.createSheet("JornalsResults");
		writeExelSheetOutputData(sheet4, jornalsResults, JORNALS_ROW_HEADERS);

		Sheet sheet5 = wb.createSheet("Results");
		writeExelSheetResultsData(sheet5, results, RESULTS_ROW_HEADERS);

		try {
			FileOutputStream fos = new FileOutputStream(new File("VKSYU.xls"));
			wb.write(fos);
			fos.close();

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return new FileInputStream(new File("VKSYU.xls"));
	}
}
